
import json
import requests

def rawdata(url):
    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko'}
    r= requests.get(url, headers=headers)
    html= r.text
    start= html.find('var trackpollBootstrap')+len('var trackpollBootstrap')+3
    end = html[start:].find('</script>')
    raw_data= html[start: start+end-1]
    return raw_data

def save_rawdata( flight_id, data):
    cnx = mysql.connector.connect(user='flightdata', password='flightdata',
                                  host='127.0.0.1',
                                  database='flightdata')
    cursor = cnx.cursor()
    update_flight = "update  flight set  raw_data= %s where id=%s"
    cursor.execute(update_flight, (data, flight_id))
    cnx.commit()
    cursor.close()
    cnx.close()
    
def parse_flight(url):
    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko'}
    r= requests.get(url, headers=headers)
    html= r.text
    start= html.find('var trackpollBootstrap')+len('var trackpollBootstrap')+3
    end = html[start:].find('</script>')
    raw_data= html[start: start+end-1]
    json_data= json.loads(raw_data)
    for k in json_data['flights'].keys():
        data={}
        data['date_d'] = url.split('/')[7]
        data['time_d'] = url.split('/')[8]
        
        flight_data= json_data['flights'][k]
        data['cancelled']=flight_data['cancelled']
        data['status']= flight_data['flightStatus']
        data['airport_s3']= flight_data['origin']['iata']
        data['airport_d3']= flight_data['destination']['iata']
        
        data['takeoff_s']= flight_data['takeoffTimes']['scheduled']
        data['takeoff_e']= flight_data['takeoffTimes']['estimated']
        data['takeoff_a']= flight_data['takeoffTimes']['actual']
        data['delay'] = data['takeoff_a'] - data['takeoff_s']
        data['landing_s']= flight_data['landingTimes']['scheduled']
        data['landing_e']= flight_data['landingTimes']['estimated']
        data['landing_a']= flight_data['landingTimes']['actual']
        data['raw']= raw_data
        
        return data

import mysql.connector

def save_flight(flight_id, data):
    
    cnx = mysql.connector.connect(user='flightdata', password='flightdata',
                                  host='127.0.0.1',
                                  database='flightdata')
    cursor = cnx.cursor()
    
    update_flight = "update  flight \
                  set  raw_data= %s , cancelled=%s, status=%s, \
                  date_d=%s, time_d=%s, airport_s3=%s, airport_d3=%s, \
                  takeoff_s=%s, takeoff_e=%s,takeoff_a=%s, delay=%s,\
                    landing_s=%s, landing_e=%s,landing_a=%s \
                where id=%s"
   
    cursor.execute(update_flight, (data['raw'],data['cancelled'], data['status'],
                                   data['date_d'], data['time_d'],data['airport_s3'], data['airport_d3'], 
                                   data['takeoff_s'], data['takeoff_e'], data['takeoff_a'], data['delay'],
                                   data['landing_s'], data['landing_e'], data['landing_a'],flight_id))

    cnx.commit()

    cursor.close()
    cnx.close()

    

if __name__ == "__main__":

    cnx = mysql.connector.connect(user='flightdata', password='flightdata',
                                  host='127.0.0.1',
                                  database='flightdata')
    cursor = cnx.cursor()
    #cursor.execute("select id,url from flight where date_d is  null  limit 10 ")
    #cursor.execute("select id, url from flight where raw_data is  null group by fleet_id ")
    cursor.execute("select id, url from flight where raw_data is  null ")
    for (id,url) in cursor:
        print (id,url)
        url= 'https://zh.flightaware.com' +url
        data= rawdata(url)
        print(len(data))
        save_rawdata( id, data)
        # try:
        #     save_rawdata(cnx,id, rawdata(url))
        # except  Exception as e:
        #     print('Error: '+ str(e))

    # for (id, url) in cursor:
    #     print (id,url)
    #     url= 'https://zh.flightaware.com' +url
    #     try:
    #         data= parse_flight(url)
    #         save_flight(id, data)
    #     except  Exception as e:
    #         print('Error: '+ str(e))